package com.hst.capacity.infra.persistence.jpa.setting.station;

import com.hst.capacity.domain.model.entity.setting.station.SysRegionEntity;
import com.hst.capacity.domain.model.response.setting.station.CityVO;
import com.hst.capacity.domain.model.response.setting.station.DistrictVO;
import com.hst.capacity.domain.model.response.setting.station.ProvinceVO;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.CrudRepository;
import org.springframework.data.repository.query.Param;

import java.util.List;

public interface SysRegionRepository extends CrudRepository<SysRegionEntity, String> {
    SysRegionEntity findByCode(String code);

    /**
     * 已开通城市
     * @return
     */
    @Query("select o from SysRegionEntity o where o.isInService = 1 order by o.parentId,o.code")
    List<SysRegionEntity> findInServiceCityList();

    /**
     * 已开通省市
     * @return
     */
    @Query("select o " +
            "from SysRegionEntity o " +
            "where o.code in(select parentId from SysRegionEntity where isInService=1) " +
            "and o.parentId is null " +
            "order by o.code")
    List<SysRegionEntity> findInServiceProvinceList();

    /**
     * 已开通省直辖市
     * @return
     */
    @Query("select new com.hst.capacity.domain.model.response.setting.station.ProvinceVO(o.code,o.name) " +
            "from SysRegionEntity o " +
            "where o.code in(select parentId from SysRegionEntity where isInService=1) " +
            "and o.parentId is null " +
            "order by o.code")
    List<ProvinceVO> findProvinceList();

    /**
     * 已开通城市
     * @return
     */
    @Query("select new com.hst.capacity.domain.model.response.setting.station.CityVO(o.code,o.name) " +
            "from SysRegionEntity o where o.isInService = 1 and o.parentId=:parentId order by o.parentId,o.code")
    List<CityVO> findCityListByParentId(@Param("parentId")String parentId);

    /**
     * 已开通省市的区县
     * @return
     */
    @Query("select new com.hst.capacity.domain.model.response.setting.station.DistrictVO(o.code,o.name) " +
            "from SysRegionEntity o where o.parentId=:parentId order by o.code")
    List<DistrictVO> findDistrictListByParentId(@Param("parentId")String parentId);

    /**
     * 已开通省市的区县
     * @return
     */
    @Query("select o " +
            "from SysRegionEntity o " +
            "where o.parentId in(select parentId from SysRegionEntity where isInService=1) " +
            "and o.parentId is not null " +
            "order by o.parentId,o.code")
    List<SysRegionEntity> findInServiceDistrictList();

    /**
     * 已开通城市
     * @return
     */
    @Query("select o from SysRegionEntity o where o.isInService = 1 and o.parentId=:parentId order by o.parentId,o.code")
    List<SysRegionEntity> findInServiceCityListByParentId(@Param("parentId")String parentId);

    /**
     * 已开通城市
     * @return
     */
    @Query("select o from SysRegionEntity o where o.parentId=:parentId order by o.code")
    List<SysRegionEntity> findInServiceDistrictListByParentId(@Param("parentId")String parentId);

    /**
     * 根据城市编码获取城市信息
     * @return
     */
    @Query("select o from SysRegionEntity o where o.cityCode=:cityCode order by o.code")
    List<SysRegionEntity> findRegionByCityCode(@Param("cityCode")String cityCode);

}
